*Monthly employment and wages, National Security Sector

cd "C:\Users\MurphyD\Documents\BLSemployment\County employment"
clear
set more off
*To run the commented out code, first download the singlefiles from the QCEW**********
/*
forvalues yr = 2002 (1) 2013 {
  insheet using `yr'q1_q4singlefile.csv, clear
  keep if agglvl_code==74 | agglvl_code==75  | agglvl_code==76 |  agglvl_code==78 | (industry=="10" & own==0)
  generate State = 1 if own_code== 1 | own_code==2 | own_code==3
    replace State = 0 if own_code==5
  destring industry_code, generate (industry) force
  keep if industry>=10 | industry==.
      replace Ind=928 if industry==928
      *National Security
    collapse (sum) month1_emplvl month2_emplvl month3_emplvl total_qtrly_wages taxable_qtrly_wages (mean) avg_wkly_wage, by( area_fips Ind year qtr)
    drop if Ind==. 

   capture append using "county industry employmentINDGVT.dta"
  describe, short
  save "county industry employmentINDGVT.dta", replace
  }
*/

use "county industry employmentINDGVT.dta", clear 
 
rename Ind ind
destring area_fips, replace force
rename area_fips fips
rename qtr quarter

*rename month3_emp emp
egen emp=rowmean(month1_emp month2_emp month3_emp)
rename avg_wkly wage
rename total_qtrly_wages inc

sort fips ind year quarter

save tempInd, replace
  ***will have emp and wage by industry, ownership code
drop if emp==0
drop if wage==0
  *drop month1 and 2 employment
drop month* taxable
drop if fips==.
reshape wide emp wage inc, i(fips year quarter ) j(ind)


destring fips, replace force
drop if fips==.

*county annual
collapse (mean) emp* (sum) wage* inc*, by(fips year)

*cbsa annual
merge m:1 fips using countyCBSAmap.dta
keep if cbsa<.
collapse (sum) emp* inc*, by(cbsa year)
save CBSAempAnnGvt, replace

